package light.head.dao.inv;

import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Service;

import light.head.constant.Sys;
import light.head.framework.base.BaseDAO;
import light.head.model.inv.InvStorageDetail;
@Service
public class InvStorageDetailDAO extends BaseDAO<InvStorageDetail, Integer>{
	private final static String sql_findByIsc = "FROM InvStorageDetail isd WHERE isd.deleted = 0 AND isd.isc = ?";
	private final static String sql_findBySerial = "FROM InvStorageDetail isd WHERE isd.serial = ? AND isd.deleted = 0";
	private final static String sql_findBySerialCcDc = "FROM InvStorageDetail isd WHERE isd.deleted = 0 AND isd.serial = ? AND isd.isc IN (select ins.code FROM InvStorage ins WHERE ins.cc= ? AND ins.dc = ?)";
	private final static String sql_findBySerialCc = "FROM InvStorageDetail isd WHERE isd.deleted = 0 AND isd.serial = ? AND isd.isc IN (select ins.code FROM InvStorage ins WHERE ins.cc= ?)";
	/**
	 * 通过库存编号获取序列号
	 * @param isc			库存编号
	 */
	public List<InvStorageDetail> findByIsc(Integer isc) {
		if (null == isc) return null;
		return find(sql_findByIsc, null, null, isc);
	}
	/**
	 * 
	 */
	public List<InvStorageDetail> findBySerial(String serial){
		if(null == serial) return null;
		return find(sql_findBySerial, null, null, serial);
	}
	/**
	 * 
	 */
	public Object findBySerial(String serial, List<Integer> ocs){
		if(null == serial || null == ocs || ocs.size() == 0) return null;
		StringBuffer sql = new StringBuffer("select ins.cc, ici.name, ins.snc, ici.price, insd.serial, ins.dc, ind.name as indname from InvStorage ins join InvStorageDetail insd on ins.code = insd.isc ");
		List<Object> parm = new ArrayList<Object>();
		sql.append(" left join InvCommodityItem ici on ins.cc = ici.code");
		sql.append(" left join InvDepot ind on ins.dc = ind.code");
		sql.append(" where insd.deleted = 0 AND insd.serial = ? and ins.oc IN (");
		parm.add(serial);
		for (Integer i : ocs) {
			sql.append("?,");
			parm.add(i);
		}
		sql.deleteCharAt(sql.length() - 1).append(")");

		
		List<Object> list = query(sql.toString(), null, 1, parm.toArray());	
		if (null == list || list.size() == 0) 
			return null;
		
		return list.get(0);
	}
	/**
	 * 根据序列号商品编号仓库编号查询是否有数据
	 */
	public List<InvStorageDetail> findBySerialCcDc(String serial, Integer cc, Integer dc){
		if(null == serial || null == cc || null == dc) return null;
		return find(sql_findBySerialCcDc,  null, null,serial,cc,dc);
	}
	/**
	 * 根据序列号商品编号查询是否有数据
	 */
	public List<InvStorageDetail> findBySerialCc(String serial, Integer cc){
		if(null == serial || null == cc) return null;
		return find(sql_findBySerialCc,  null, null,serial,cc);
	}
	/**
	 * 
	 */
	public String findByCcOc(Integer cc, List<Integer> ocs){
		if( null == ocs || null == cc) return null;
		StringBuffer sql = new StringBuffer("select isd.serial FROM InvStorageDetail isd WHERE isd.isc IN (SELECT isi.code FROM InvStorage isi WHERE isi.cc = ? AND isi.oc IN (");
		List<Object> list = new ArrayList<Object>();
		list.add(cc);
		for (Integer i : ocs) {
			sql.append("?,");
			list.add(i);
		}
		sql.deleteCharAt(sql.length() - 1).append("))");
		List<Object> objList = query(sql.toString(), null, 1, list.toArray());	
		if (null == objList || objList.size() == 0) 
			return null;
		
		return (null != objList.get(0) && !Sys.NONE.equals(objList.get(0))) ? objList.get(0).toString() : null;
	}
	/**
	 * 
	 */
	public Object findBySerialDc(String serial,Integer dc, List<Integer> ocs){
		if(null == serial || null == ocs || ocs.size() == 0 || dc == null) return null;
		StringBuffer sql = new StringBuffer("select ins.cc, ici.name, ins.snc, ici.price, insd.serial, ins.dc, ind.name as indname,ins.count from InvStorage ins join InvStorageDetail insd on ins.code = insd.isc ");
		List<Object> parm = new ArrayList<Object>();
		sql.append(" left join InvCommodityItem ici on ins.cc = ici.code");
		sql.append(" left join InvDepot ind on ins.dc = ind.code");
		sql.append(" where insd.deleted = 0 AND insd.serial = ? and ins.dc =? and ins.oc IN (");
		parm.add(serial);
		parm.add(dc);
		for (Integer i : ocs) {
			sql.append("?,");
			parm.add(i);
		}
		sql.deleteCharAt(sql.length() - 1).append(")");
		
		List<Object> list = query(sql.toString(), null, 1, parm.toArray());	
		if (null == list || list.size() == 0) 
			return null;
		
		return list.get(0);
	}
	/**
	 * 根据序列号号查询是否有相同的序列号 有就删除
	 */
	public List<InvStorageDetail> findByOc(String serial, List<Integer> ocs){
		if( null == ocs || null == serial) return null;
		StringBuffer hql = new StringBuffer(" FROM InvStorageDetail isd WHERE isd.serial = ? AND isd.isc IN (SELECT isi.code FROM InvStorage isi WHERE isi.oc IN (");
		List<Object> list = new ArrayList<Object>();
		list.add(serial);
		for (Integer i : ocs) {
			hql.append("?,");
			list.add(i);
		}
		hql.deleteCharAt(hql.length() - 1).append("))");
		return find(hql.toString(), list.toArray());
	}
}
